package com.cn.yang.dao.impl;

import com.cn.yang.dao.UserDao;
import com.cn.yang.model.UserModel;
import com.cn.yang.util.JDBC;
import com.cn.yang.util.PageUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    JDBC jdbc = new JDBC();

//新增用户
    @Override
    public int registerUser(UserModel userModel) {
        String sql = "insert into user (`username`,`password`,`phone`,`sex`,`birthday`,`hobby`,`introduce`,`createtime`,`updatetime`)" +
                "values('"+ userModel.getUsername()+"'," +
                "'"+ userModel.getPassword()+"'," +
                "'"+ userModel.getPhone()+"'," +
                "'"+ userModel.getSex()+"'," +
                "'"+ userModel.getBirthday()+"'," +
                "'"+ userModel.getHobby()+"'," +
                "'"+ userModel.getIntroduce()+"'," +
                "'"+ userModel.getCreatetime()+"'," +
                "'"+ userModel.getUpdatetime()+"')";
        UserDaoImpl registerDao = new UserDaoImpl();
        if (!registerDao.selectbyUserName(userModel)){
            int a = jdbc.update(sql);
            jdbc.closeConn();
            return a;
        }else{
            jdbc.closeConn();
            return -1;
        }
    }
//查询用户是否有值
    @Override
    public boolean selectbyUserName(UserModel userModel) {
        String sql = "select `username` from user where `username` = '"+ userModel.getUsername()+"'";
        ResultSet resultSet =jdbc.getResultSet(sql);
        boolean a = false;
        try {
            a = resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return a;
    }
//登录
    @Override
    public int login(UserModel userModel) {
        UserDaoImpl registerDao = new UserDaoImpl();
        registerDao.selectbyUserName(userModel);
        if (!registerDao.selectbyUserName(userModel)){
            return -1;
        }else {
            String sql = "select `username`,`password` from `user` where `username` = '"+ userModel.getUsername()+"'" +
                    "and `password` = '"+ userModel.getPassword()+"'";
            ResultSet resultSet =jdbc.getResultSet(sql);
            int a = 0;
            try{
                if (resultSet.next()){
                    a=1;
                }else {
                    a=2;
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            jdbc.closeConn();
            return a;
        }
    }
//查询所有数据
    @Override
    public List<UserModel> findAllUser(UserModel userModel) {
        String sql= "select * from user ";
        if ((!"".equals(userModel.getSortUserUtil().getSortId()))||(!"".equals(userModel.getSortUserUtil().getSortUserName()))||(!"".equals(userModel.getSortUserUtil().getSortSex()))||(!"".equals(userModel.getSortUserUtil().getSortBirthday()))){
            sql +=" order by ";
            if (!"".equals(userModel.getSortUserUtil().getSortId())){
                sql +=" id "+userModel.getSortUserUtil().getSortId()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortUserName())){
                sql +=" username "+userModel.getSortUserUtil().getSortUserName()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortSex())){
                sql +=" sex "+userModel.getSortUserUtil().getSortSex()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortBirthday())){
                sql +=" birthday "+userModel.getSortUserUtil().getSortBirthday()+"";
            }
        }
        sql+=" limit "+userModel.getPageUtil().getPageNumber()+","+userModel.getPageUtil().getPageSize()+"";
        List<UserModel> list = new ArrayList<>();
        ResultSet resultSet = jdbc.getResultSet(sql);
        try {
            while (resultSet.next()){
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setIntroduce(resultSet.getString("introduce"));
                model.setCreatetime(resultSet.getString("createtime"));
                model.setUpdatetime(resultSet.getString("updatetime"));
                list.add(model);
            }
        }catch (SQLException e){
            e.getMessage();
        }
        jdbc.closeConn();
        return list;
    }
    //删除一条数据
    @Override
    public int deleteById(Integer id) {
        String sql = "delete from user where id ="+id;
        int result = jdbc.update(sql);
        if (result==1){
            jdbc.closeConn();
            return 1;
        }else {
            jdbc.closeConn();
            return 0;
        }
    }
//查询一条数据
    @Override
    public UserModel findIdUser(Integer id) {
        String sql = "select * from user where id="+id;
        ResultSet resultSet = jdbc.getResultSet(sql);
        UserModel userModel = new UserModel();
        try {
            while (resultSet.next()){
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPassword(resultSet.getString("password"));
                userModel.setPhone(resultSet.getString("phone"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setIntroduce(resultSet.getString("introduce"));
                userModel.setCreatetime(resultSet.getString("createtime"));
                userModel.setUpdatetime(resultSet.getString("updatetime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbc.closeConn();
        }
        return userModel;
    }
//更新数据库  用户数据============================
    @Override
    public int updateUser(UserModel userModel) {
        String sql = "update `user` set `password`='"+userModel.getPassword()+"'" +
                ",`phone`='"+userModel.getPhone()+"' " +
                ",`sex`='"+userModel.getSex()+"'" +
                ",`birthday`='"+userModel.getBirthday()+"'" +
                ",`hobby`='"+userModel.getHobby()+"'" +
                ",`introduce`='"+userModel.getIntroduce()+"'" +
                ",`updatetime`='"+userModel.getUpdatetime()+"'" +
                "where id =" +userModel.getId();
        int result = jdbc.update(sql);
        jdbc.closeConn();
        //成功为1， 失败为0
        return result;
    }
//模糊查询
    @Override
    public List<UserModel> findSomeUser(UserModel userModel) {
        String sql = "select * from user where 1=1 ";
        if (!"".equals(userModel.getUsername())){
            sql +="and username like '%"+userModel.getUsername()+"%'";
        }
        if (!"".equals(userModel.getPhone())){
            sql +="and phone like '%"+userModel.getPhone()+"%'";
        }
        if (!"".equals(userModel.getSex())){
            sql +="and sex like '%"+userModel.getSex()+"%'";
        }
        if (!"".equals(userModel.getHobby())){
            sql +="and hobby like '%"+userModel.getHobby()+"%'";
        }

        if ((!"".equals(userModel.getFirstbirthday()))&&(!"".equals(userModel.getLastbirthday()))){
            sql +="and birthday between '"+userModel.getFirstbirthday()+"' and '"+userModel.getLastbirthday()+"'";
        }
        if ((!"".equals(userModel.getFirstbirthday()))&&("".equals(userModel.getLastbirthday()))){
            sql +="and birthday >= '"+userModel.getFirstbirthday()+"'";
        }
        if (("".equals(userModel.getFirstbirthday()))&&(!"".equals(userModel.getLastbirthday()))){
            sql +="and birthday <= '"+userModel.getLastbirthday()+"'";
        }
        if ((!"".equals(userModel.getSortUserUtil().getSortId()))||(!"".equals(userModel.getSortUserUtil().getSortUserName()))||(!"".equals(userModel.getSortUserUtil().getSortSex()))||(!"".equals(userModel.getSortUserUtil().getSortBirthday()))||(!"".equals(userModel.getSortUserUtil().getSortCreatetime()))||(!"".equals(userModel.getSortUserUtil().getSortUpdatetime()))){
            sql +=" order by ";
            if (!"".equals(userModel.getSortUserUtil().getSortId())){
                sql +=" id "+userModel.getSortUserUtil().getSortId()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortUserName())){
                sql +=" username "+userModel.getSortUserUtil().getSortUserName()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortSex())){
                sql +=" sex "+userModel.getSortUserUtil().getSortSex()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortBirthday())){
                sql +=" birthday "+userModel.getSortUserUtil().getSortBirthday()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortCreatetime())){
                sql +=" createtime "+userModel.getSortUserUtil().getSortCreatetime()+"";
            }
            if (!"".equals(userModel.getSortUserUtil().getSortUpdatetime())){
                sql +=" updatetime "+userModel.getSortUserUtil().getSortUpdatetime()+"";
            }
        }
        sql+=" limit "+userModel.getPageUtil().getPageNumber()+","+userModel.getPageUtil().getPageSize()+"";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()){
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setIntroduce(resultSet.getString("introduce"));
                model.setCreatetime(resultSet.getString("createtime"));
                model.setUpdatetime(resultSet.getString("updatetime"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return list;
    }
//总条数
    @Override
    public int countUser() {
        String sql = "SELECT COUNT(*) as count FROM user";
        ResultSet resultSet = jdbc.getResultSet(sql);
        int a = 0;
        try{
            UserModel model = new UserModel();
            while (resultSet.next()){
                model.setCount(resultSet.getInt("count"));
                a = model.getCount();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return a;
    }
}
